package com.qf.java2105.huangchuang.hotel.dao.impl;

import com.qf.java2105.huangchuang.hotel.dao.IFoodDao;
import com.qf.java2105.huangchuang.hotel.pojo.Food;
import com.qf.java2105.huangchuang.hotel.utils.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 菜品持久层实现
 * @author hcvg
 * @version v1.0
 * @date 2021/09/12
 **/
public class FoodDaoImpl implements IFoodDao {
    private QueryRunner queryRunner = null;

    @Override
    public List<Map<String, Object>> findByFoodName(String foodName) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "SELECT\n" +
                "  food_id foodId,\n" +
                "  f.type_id typeId,\n" +
                "  food_name foodName,\n" +
                "  food_price foodPrice,\n" +
                "  food_mprice foodMprice,\n" +
                "  food_image foodImage,\n" +
                "  food_desc foodDesc,\n" +
                "  ft.type_name typeName\n" +
                "FROM\n" +
                "  t_food f,\n" +
                "  t_food_type ft\n" +
                "WHERE f.type_id = ft.type_id\n" +
                "  AND f.food_name like ?";
        return queryRunner.query(sql, new MapListHandler(),foodName);
    }

    @Override
    public void save(Food food) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql= "INSERT INTO t_food (\n" +
                "  type_id,\n" +
                "  food_name,\n" +
                "  food_price,\n" +
                "  food_mprice,\n" +
                "  food_image,\n" +
                "  food_desc\n" +
                ")\n" +
                "VALUES\n" +
                "  (?,?,?,?,?,?)";
        queryRunner.update(JdbcUtil.getConnection(), sql, food.getTypeId(), food.getFoodName(), food.getFoodPrice(),
                food.getFoodMprice(), food.getFoodImage(), food.getFoodDesc());
    }

    @Override
    public Food findById(Integer id) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "SELECT\n" +
                "  food_id foodId,\n" +
                "  type_id typeId,\n" +
                "  food_name foodName,\n" +
                "  food_price foodPrice,\n" +
                "  food_mprice foodMprice,\n" +
                "  food_image foodImage,\n" +
                "  food_desc foodDesc\n" +
                "FROM\n" +
                "  t_food\n" +
                "WHERE food_id = ?";
        return queryRunner.query(sql, new BeanHandler<>(Food.class), id);
    }

    @Override
    public void deleteById(Integer id) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtil.getDataSource());

        String sql = "DELETE FROM t_food WHERE food_id = ?";

        queryRunner.update(JdbcUtil.getConnection(), sql, id);
    }

    @Override
    public void update(Food food) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "UPDATE\n" +
                "  t_food\n" +
                "SET\n" +
                "  type_id = ?,\n" +
                "  food_name = ?,\n" +
                "  food_price = ?,\n" +
                "  food_mprice = ?,\n" +
                "  food_image = ?,\n" +
                "  food_desc = ?\n" +
                "WHERE food_id = ?";
        queryRunner.update(sql, food.getTypeId(), food.getFoodName(), food.getFoodPrice(),
            food.getFoodMprice(), food.getFoodImage(), food.getFoodDesc(), food.getFoodId());
    }

    @Override
    public Integer foodNameExist(String foodName) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        String sql =  "SELECT food_id FROM t_food WHERE food_name = ?";
        return queryRunner.query(sql, new ScalarHandler<>(),  foodName);
    }

    @Override
    public Long countByCondition(Integer typeId, String foodName) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        StringBuffer sql = new StringBuffer("SELECT COUNT(1) FROM t_food ");
        if (typeId == 0){
            sql.append("WHERE food_name LIKE ?");
            return queryRunner.query(sql.toString(), new ScalarHandler<>(), foodName);
        }else {
            sql.append("WHERE type_id = ? AND food_name LIKE ?");
            return queryRunner.query(sql.toString(), new ScalarHandler<>(), typeId, foodName);
        }

    }

    @Override
    public List<Food> findByPageWithCondition(Integer start, Integer pageSize, Integer typeId, String foodName) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtil.getDataSource());
        StringBuffer sql = new StringBuffer("SELECT\n" +
                "  food_id foodId,\n" +
                "  type_id typeId,\n" +
                "  food_name foodName,\n" +
                "  food_price foodPrice,\n" +
                "  food_mprice foodMprice,\n" +
                "  food_image foodImage,\n" +
                "  food_desc foodDesc\n" +
                "FROM\n" +
                "  t_food \n");
        if (typeId == 0){
            sql.append("WHERE food_name LIKE ? LIMIT ?, ?");
            return queryRunner.query(sql.toString(), new BeanListHandler<>(Food.class), foodName, start, pageSize);
        }else{
            sql.append("WHERE type_id = ? AND food_name LIKE ? LIMIT ?, ?");
            return queryRunner.query(sql.toString(), new BeanListHandler<>(Food.class), typeId, foodName, start, pageSize);
        }
    }
}
